package ibmetp.mis.dao;

import ibmetp.mis.vo.Page;

import ibmetp.mis.vo.SysParam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * @author ZHANG YU TING
 *
 */

public class SysParamDao extends BaseDao {
	private Connection conn;
	
	/**
	 * 通过角色id获取角色信息
	 * @param id
	 * @return
	 */
	public SysParam getSysParamById(Long id)
	{
		String sql="select r.* from BAS_T_SYSPARAM r where r.SYSPARAM_ID=?";
		System.out.println(sql);
		conn=DBUtil.getConnection();
		PreparedStatement ps;
		SysParam sysparam=null;
		SysParamDao ud=new SysParamDao();
		try{
			ps=conn.prepareStatement(sql);
			ps.setLong(1, id);
			ResultSet rs=ps.executeQuery();
			while(rs!=null && rs.next())
			{
				sysparam=new SysParam();
				sysparam.setSysparamId(rs.getLong("SYSPARAM_ID"));
				sysparam.setSysparamName(rs.getString("SYSPARAM_NAME"));
				sysparam.setValue(rs.getString("VALUE"));
				sysparam.setCategory(rs.getString("CATEGORY"));
		/*		sysparam.setCreator(ud.getUserById(rs.getLong("CREATE_BY")));
				sysparam.setCreateDate(rs.getDate("CREATE_DATE"));
				sysparam.setUpdater(ud.getUserById(rs.getLong("UPDATE_BY")));
				sysparam.setUpdateDate(rs.getDate("UPDATE_DATE"));*/
			}
			ps.close();
			conn.close();			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return sysparam;
	}
	

	/**
	 * 通过调用对象sysparamVO添加数据
	 * @param sysparamVO
	 * @return
	 */
	public String addSysParam(SysParam sysparamVO) 
	{
//		String addSQL="insert into SYSPARAM (SYSPARAM_ID,SYSPARAM_NAME,VALUE,CATEGORY,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE)" +
//						" values(?,?,?,?,?,?,?,?)";
		String addSQL="insert into BAS_T_SYSPARAM (SYSPARAM_ID,SYSPARAM_NAME,VALUE,CATEGORY)" +
		" values(PARAM_SEQ.NEXTVAL,?,?,?)";
		conn=DBUtil.getConnection();
		try{
			PreparedStatement ps= conn.prepareStatement(addSQL);
			//ps.setLong(1, sysparamVO.getSysparamId());
			ps.setString(1, sysparamVO.getSysparamName());
			ps.setString(2, sysparamVO.getValue());
			ps.setString(3, sysparamVO.getCategory());
/*			ps.setLong(4, sysparamVO.getCreator().getUserId());
			ps.setDate(5, new java.sql.Date(sysparamVO.getCreateDate().getTime()));
			ps.setLong(6, sysparamVO.getUpdater().getUserId());
			ps.setDate(7, new java.sql.Date(sysparamVO.getCreateDate().getTime()));*/
			ResultSet rs = ps.executeQuery();
			ps.close();
			conn.close();
//		return ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 通过userIds删除数据
	 * @param userIds
	 * @return
	 * @throws SQLException
	 */
	
	public void deleteSysParamById(String SysParamIds)
	{
		String delSQL="delete from BAS_T_SYSPARAM where SYSPARAM_ID in ("+SysParamIds+")";
//		String delSQL="delete from BAS_T_SYSPARAM where USER_ID in ("+array2String(SysParamIds)+")";
		conn=DBUtil.getConnection();
		PreparedStatement ps;
		try {
			ps=conn.prepareStatement(delSQL);
			ResultSet rs=ps.executeQuery();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 通过调用对象sysparamVO更新数据
	 * @param sysparamVO
	 * @throws SQLException
	 */
	
	public void updateSysParam(SysParam sysparamVO)
	{
//		String addSQL="update SYSPARAM set (SYSPARAM_ID=?,SYSPARAM_NAME=?,VALUE,CATEGORY=?,CREATE_BY=?,CREATE_DATE=?,UPDATE_BY=?,UPDATE_DATE=?)" +
//						" where SYSPARAM_ID=?";
		String updSQL="update BAS_T_SYSPARAM set SYSPARAM_NAME=?,VALUE=?,CATEGORY=? where SYSPARAM_ID=?";
		conn=DBUtil.getConnection();
		try{
			PreparedStatement ps= conn.prepareStatement(updSQL);

			ps.setString(1, sysparamVO.getSysparamName());
			ps.setString(2, sysparamVO.getValue());
			ps.setString(3, sysparamVO.getCategory());
	/*		ps.setLong(4, sysparamVO.getCreator().getUserId());
			ps.setDate(6, new java.sql.Date(sysparamVO.getCreateDate().getTime()));
			ps.setLong(7, sysparamVO.getUpdater().getUserId());
			ps.setDate(8, new java.sql.Date(sysparamVO.getCreateDate().getTime()));*/
			ps.setLong(4, sysparamVO.getSysparamId());
			ResultSet rs = ps.executeQuery();
			ps.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	 /**
	  * 数组转换为字符串
	  * @param strs
	  * @return
	  */
	  
	public static String array2String(String[] strs)
	{
		if(strs==null)
		{
			return "";
		}
		String result="";
		for (String string : strs) {
			
			result+=string+",";
		}
		return result.substring(0,result.length()-1);
	}
	
	/**
	 * 分页查询
	 * @param pageNo
	 * @param pageSize
	 * @param sysparam
	 * @return
	 */
	public Page getSysParamPage(int pageNo,int pageSize,Object sysparam)
	{		
		return this.find(sysparam, pageNo, pageSize, "BAS_T_SYSPARAM");		
	}
	
	
	@Override
	/**
	 * 将查询结果转换成sysparam类集，并保存进List集合中
	 */
	public List ResultSet2List(ResultSet rs) {
		// TODO Auto-generated method stub
		List<SysParam> resultList=new ArrayList<SysParam>();
		try {
			while(rs!=null&&rs.next())
			{
				SysParam sysparam=new SysParam();
				sysparam.setSysparamId(rs.getLong("SYSPARAM_ID"));
				sysparam.setSysparamName(rs.getString("SYSPARAM_NAME"));
				sysparam.setValue(rs.getString("VALUE"));
				sysparam.setCategory(rs.getString("CATEGORY"));
				resultList.add(sysparam);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultList;
	}
	
	@Override
	/**
	 * 添加查询条件
	 */
	public Map<Integer, Object[]> getCondition(Object o) {
		// TODO Auto-generated method stub
		Map<Integer,Object[]> paramsMap=new HashMap<Integer,Object[]>();
		Integer count=0;
		SysParam sysparam=(SysParam)o;
		if(sysparam.getSysparamId()!=null&&sysparam.getSysparamId()>0)
		{
			count++;
			paramsMap.put(count, new Object[]{" and SYSPARAM_ID LIKE ?","%"+sysparam.getSysparamId()+"%"});
		}
		if(sysparam.getSysparamName()!=null&&sysparam.getSysparamName().trim().length()>0)
		{
			count++;
			paramsMap.put(count, new Object[]{"and SYSPARAM_NAME LIKE ?","%"+sysparam.getSysparamName()+"%"});
		}
		return paramsMap;
	}
	
	
}